﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace CentralOffice.WebClient
{
    public partial class ExecuteResult : System.Web.UI.Page
    {
        //private const string Select_Columns = "select TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT from  information_schema.COLUMNS  WHERE TABLE_SCHEMA = 'pcmisdb' AND TABLE_NAME = '{0}'";

        private const string Select_Columns = "sp_MShelpcolumns '{0}', null, 'id', 1";
        private string TableName = null;
        protected void Page_Load(object sender, EventArgs e)
        {
            TableName = Request.QueryString["tableschema"] + "." + Request.QueryString["tablename"];
            if (!IsPostBack)
            {
                if (!String.IsNullOrEmpty(Request.QueryString["tablename"]))
                {
                    try
                    {
                        DataTable dt = null;
                        if (String.IsNullOrEmpty(Request.QueryString["rows"]))
                        {
                            lblTitle.Text = "表 " + Request.QueryString["tablename"] + " 的各列如下：";
                            dt = Husb.DataUtil.DataAccessUtil.GetDataTable(String.Format(Select_Columns, Request.QueryString["tableschema"] + "." + Request.QueryString["tablename"]), false);
                            //dt = ExecuteDataTable(String.Format(Select_Columns, Request.QueryString["tablename"]));
                        }
                        else
                        {
                            lblTitle.Text = "表 " + Request.QueryString["tablename"] + " 的数据如下：";
                            dt = Husb.DataUtil.DataAccessUtil.GetDataTable(String.Format("SELECT * FROM {0}",  Request.QueryString["tableschema"] + "." + Request.QueryString["tablename"]), false);
                        }

                        GridView1.DataSource = dt;
                        GridView1.DataBind();
                    }
                    catch (Exception ex)
                    {
                        lblError.Text = ex.Message;
                    }
                }
            }
        }

        private static DataTable ExecuteDataTable(string sql)
        {
            DataTable dt = null;
            try
            {
                Database db = DatabaseFactory.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                DataSet ds = db.ExecuteDataSet(cmd);
                if (ds != null && ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                }
            }
            catch
            {
                throw;
            }
            finally
            {
            }
            return dt;
        }
        private static int ExecuteNoQuery(string sql)
        {
            int cnt = 0;
            try
            {
                Database db = DatabaseFactory.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                cnt = db.ExecuteNonQuery(cmd);

            }
            catch
            {
                throw;
            }
            finally
            {
            }
            return cnt;
        }

        protected void Button4_Click(object sender, EventArgs e)
        {
            try
            {
                if (TextBox1.Text.Length == 0) return;
                if (TextBox1.Text.Trim().ToLower().StartsWith("select"))
                {
                    GridView1.DataSource = ExecuteDataTable(TextBox1.Text);
                    GridView1.DataBind();
                }
                else
                {
                    lblError.Text = "影响的记录数为： " + ExecuteNoQuery(TextBox1.Text);
                }
            }
            catch (Exception ex)
            {
                lblError.Text = ex.Message;
            }

            //DataBound();
        }
        protected void btnSelect_Click(object sender, EventArgs e)
        {
            string s = GenerateFieldNames(TableName);

            TextBox1.Text = "Select " + s + " FROM " + TableName + " ";
        }
        protected void btnInsert_Click(object sender, EventArgs e)
        {
            TextBox1.Text = "INSERT INTO " + TableName
                + GenerateFieldNames(TableName)
                + " VALUES () ";
        }
        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            TextBox1.Text = "UPDATE " + TableName + " SET "
                + GenerateUpdateNames(TableName)
                + " ";
        }
        protected void btnDelete_Click(object sender, EventArgs e)
        {
            TextBox1.Text = "DELETE FROM " + TableName + " WHERE Id = '' ";
        }

        private string GenerateFieldNames(string tableName)
        {
            DataTable dt = Husb.DataUtil.DataAccessUtil.GetDataTable(String.Format(Select_Columns, tableName));
            if (dt == null || dt.Rows.Count == 0) return "";
            string names = "";
            {
                foreach (DataRow row in dt.Rows)
                {
                    names += row["col_name"].ToString() + ", ";
                }
            }

            if (names.EndsWith(", "))
            {
                names = names.Remove(names.Length - 2);
            }
            return names;
        }

        private string GenerateUpdateNames(string tableName)
        {
            DataTable dt = Husb.DataUtil.DataAccessUtil.GetDataTable(String.Format(Select_Columns, tableName));
            if (dt == null || dt.Rows.Count == 0) return "";
            string names = "";
            {
                foreach (DataRow row in dt.Rows)
                {
                    names += row["col_name"].ToString() + " = '', ";
                }
            }

            if (names.EndsWith(" = '', "))
            {
                names = names.Remove(names.Length - 2);
            }

            return names + " WHERE Id = '' ";
        }
        protected void btnSchema_Click(object sender, EventArgs e)
        {
            if (!String.IsNullOrEmpty(TableName))
            {
                try
                {

                    GridView1.DataSource = Husb.DataUtil.DataAccessUtil.GetDataTable(String.Format(Select_Columns, TableName));
                    GridView1.DataBind();
                }
                catch (Exception ex)
                {
                    lblError.Text = ex.Message;
                }
            }

        }

        private void DataBound()
        {
            if (!String.IsNullOrEmpty(TableName))
            {
                try
                {
                    DataTable dt = null;
                    lblTitle.Text = "表 " + Request.QueryString["tablename"] + " 的数据如下：";
                    dt = ExecuteDataTable(String.Format("SELECT * FROM {0}", TableName));

                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
                catch (Exception ex)
                {
                    lblError.Text = ex.Message;
                }
            }
        }
        protected void btnBound_Click(object sender, EventArgs e)
        {
            DataBound();
        }
    }
}
